/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tamnhat.utility.model;

import com.tamnhat.utility.entity.SMSBatches;
import com.tamnhat.utility.entity.ServiceInfor;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import telsoft.admin.util.AppServer;
import telsoft.sql.Database;

/**
 *
 * @author Administrator
 */
public class SMSBatchesModel {
    public static ArrayList<SMSBatches> getAll(String phoneNumber,String productName) throws Exception{
        ArrayList<SMSBatches> list = new ArrayList<SMSBatches>();
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        String strSQL = " select a.sender,a.message,a.executed_time,a.delivery_status,a.content_type,a.url "
                + " from sdp_sms_batches a"
                + " where a.isdn like '%"+phoneNumber+"%'"
                + " order by a.created_time";
        try {
            cn = AppServer.getConnection();
            st = cn.createStatement();
            rs = st.executeQuery(strSQL);
            while (rs.next()) {
                SMSBatches c = new SMSBatches();
                c.setProductName(productName);
                c.setSender(rs.getString(1));
                c.setMessage(rs.getString(2));
                c.setExecutedTime(rs.getString(3));
                c.setDeliveryStatus(rs.getString(4));
                c.setContentType(rs.getString(5));
                c.setUrl(rs.getString(6));
                list.add(c);
            }            
        } finally {
            Database.closeObject(rs);
            Database.closeObject(st);
            Database.closeObject(cn);
        }
        return list;
    }
    public static ArrayList<SMSBatches> viewMsg(String phoneNumber,String productId,String productName) throws Exception{
        ArrayList<SMSBatches> list = new ArrayList<SMSBatches>();
        Connection cn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        String strSQL = " select a.sender,a.message,to_char(a.executed_time,'dd-mm-yyyy hh24:mi:ss'),nvl(a.delivery_status,'0'),a.content_type,a.url"
                + " from sdp_sms_batches a"
                + " where a.product_id = ? and a.isdn = ?"
                + " order by a.created_time";
        try {
            cn = AppServer.getConnection();
            pst = cn.prepareStatement(strSQL);
            
            pst.setString(1, productId);
            pst.setString(2, phoneNumber);
            
            rs = pst.executeQuery();
            while (rs.next()) {
                SMSBatches c = new SMSBatches();                
                c.setProductId(productId);
                c.setProductName(productName);
                c.setSender(rs.getString(1));
                c.setMessage(rs.getString(2));
                c.setExecutedTime(rs.getString(3));
                c.setDeliveryStatus(rs.getString(4));
                c.setContentType(rs.getString(5));
                c.setUrl(rs.getString(6));
                list.add(c);
            }            
        } finally {
            Database.closeObject(rs);
            Database.closeObject(pst);
            Database.closeObject(cn);
        }
        return list;
    }
}
